/*
 *
 *  This program is free software: you can redistribute it and/or modify
 *  it under the terms of the GNU General Public License as published by
 *  the Free Software Foundation, either version 3 of the License, or
 *  (at your option) any later version.
 *
 *  This program is distributed in the hope that it will be useful,
 *  but WITHOUT ANY WARRANTY; without even the implied warranty of
 *  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 *  GNU General Public License for more details.
 *
 *  You should have received a copy of the GNU General Public License
 *  along with this program.  If not, see http://www.gnu.org/licenses/. 
 *
 *  Authors:
 *  	Albert Marieges Garcia
 *  	Álex Pardo Fernández
 *  	Enric Mayas Márquez
 *  	Juan Carlos Calvo Tejedor
 *  	Marc Gironés Dezsényi
 *  
 *  Contact:
 *  	bcn.tours.android@gmail.com
 *  Webpage:
 *  	bcntours.co.cc
 *
 */
package com.BCNTours.DataBase;

import java.util.ArrayList;

import com.BCNTours.Model.Attraction;
import com.BCNTours.Model.InformationPoint;
import com.BCNTours.Model.Monument;
import com.BCNTours.Model.Museum;
import com.BCNTours.Model.Poi;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;
 
/**
 * Support class used by the DataBase to work properly.
 */
public class DataBaseHelper extends SQLiteOpenHelper {
	
	private static final String DATABASE_NAME = "BCNTours";
	private static final int DATABASE_VERSION = 1;
	
	private static final String table1Name = "preferences";
	private static final String table1Create = "CREATE TABLE " + table1Name + "(" +
    		"ID INTEGER PRIMARY KEY, " +
    		"LANGUAJE INTEGER)";
    
    private static final String table2Name = "poi";
    private static final String table2Create = "CREATE TABLE " + table2Name + "(" +
    		"ID INTEGER PRIMARY KEY, " +
    		"TYPE INTEGER, " +
    		"NAME TEXT, " +
    		"ADDRESS TEXT, " +
    		"CP TEXT, " +
    		"CITY TEXT, " +
    		"COUNTRY TEXT, " +
    		"INFO INTEGER, " +
    		"SCHEDULE INTEGER, " +
    		"TRANSPORT INTEGER, " +
    		"WEB INTEGER, " +
    		"GPSLONG REAL, " +
    		"GPSLAT REAL, " +
    		"GPSALT REAL, " +
    		"IMAGE INTEGER, " +
    		"IMAGE2 INTEGER," +
    		"LINKMOREINFORMATION INTEGER)";
    
    private static final String table3Name = "text";
    private static final String table3Create = "CREATE TABLE " + table3Name + "(" +
    		"ID INTEGER PRIMARY KEY, " +
    		"IDTEXT INTEGER, " +
    		"LANGUAJE INTEGER REFERENCES preferences(LANGUAJE), " +
    		"TEXT TEXT)";
    
    private static final String table4Name = "monument";
    private static final String table4Create = "CREATE TABLE " + table4Name + "(" +
    		"ID INTEGER PRIMARY KEY REFERENCES poi(ID), " +
    		"AUTHOR TEXT, " +
    		"PRICE INTEGER)";
    
    private static final String table5Name = "attraction";
    private static final String table5Create = "CREATE TABLE " + table5Name + "(" +
    		"ID INTEGER PRIMARY KEY REFERENCES poi(ID), " +
    		"PRICE INTEGER, " +
    		"TELF TEXT)";
    
    private static final String table6Name = "museum";
    private static final String table6Create = "CREATE TABLE " + table6Name + "(" +
		    "ID INTEGER PRIMARY KEY REFERENCES poi(ID), " +
			"EXTRA INTEGER)";
    
    private static final String table7Name = "informationPoints";
    private static final String table7Create = "CREATE TABLE " + table7Name + "(" +
		    "ID INTEGER PRIMARY KEY REFERENCES poi(ID), " +
		    "TELF TEXT, " +
			"EXTRA INTEGER)";
	
  
    
    
    /**
     * Constructor of the class
     * 
     * @param context		Context from which the class is called
     * @param name			The database name
     * @param factory		
     * @param version		The DataBase Version
     */
    public DataBaseHelper(Context context, String name, CursorFactory factory, int version) {
    	super(context, DATABASE_NAME, factory, DATABASE_VERSION);
    }
 
    
    /**
     * Execute if the database doesn't exists
     * 
     */
    @Override
    public void onCreate(SQLiteDatabase db) {
    	
    	db.execSQL("DROP TABLE IF EXISTS " + table1Name);
        db.execSQL("DROP TABLE IF EXISTS " + table2Name);
        db.execSQL("DROP TABLE IF EXISTS " + table3Name);
        db.execSQL("DROP TABLE IF EXISTS " + table3Name);
        db.execSQL("DROP TABLE IF EXISTS " + table4Name);
        db.execSQL("DROP TABLE IF EXISTS " + table5Name);
        db.execSQL("DROP TABLE IF EXISTS " + table6Name);
        db.execSQL("DROP TABLE IF EXISTS " + table7Name);
        
        db.execSQL(table1Create);
        db.execSQL(table2Create);
        db.execSQL(table3Create);
        db.execSQL(table4Create);
        db.execSQL(table5Create);
        db.execSQL(table6Create);
        db.execSQL(table7Create);
        
        InitializeDataBase init = new InitializeDataBase();
        init.initialize(db);
        
    }
    
    /**
     * Needed when using different version of the database.
     */
    @Override
    public void onUpgrade(SQLiteDatabase db, int versionAnterior, int versionNueva) {
        
    	// Removes the old version of the table
        db.execSQL("DROP TABLE IF EXISTS " + table1Name);
        db.execSQL("DROP TABLE IF EXISTS " + table2Name);
        db.execSQL("DROP TABLE IF EXISTS " + table3Name);
        db.execSQL("DROP TABLE IF EXISTS " + table3Name);
        db.execSQL("DROP TABLE IF EXISTS " + table4Name);
        db.execSQL("DROP TABLE IF EXISTS " + table5Name);
        db.execSQL("DROP TABLE IF EXISTS " + table6Name);
        db.execSQL("DROP TABLE IF EXISTS " + table7Name);
        
        // Builds the new version of the table
        db.execSQL(table1Create);
        db.execSQL(table2Create);
        db.execSQL(table3Create);
        db.execSQL(table4Create);
        db.execSQL(table5Create);
        db.execSQL(table6Create);
        db.execSQL(table7Create);
        
        // Reinserts the data
        InitializeDataBase init = new InitializeDataBase();
        init.initialize(db);
        
    }
 
    /**
     * Creates a POI, with the specified ID and in the specified language.
     * 
     * @param db 				The SQLITE object
     * @param identificador 	The id of the POI
     * @param lang 				THe language //1 Spanish, 2 Catalan, 3 English
     * @return 					The created POI
     */
    public Poi initializePoiById (SQLiteDatabase db, int identificador, int lang) {
    	Cursor c;
    	c = db.rawQuery("SELECT * FROM poi WHERE ID = '" + identificador + "'", null);
    	c.moveToFirst();
    	// poi (ID ,TYPE, NAME, ADDRESS, CP, CITY, COUNTRY, INFO, SCHEDULE, TRANSPORT, WEB, GPSLONG, GPSLAT, GPSALT, IMAGE, IMAGE2, LINKMOREINFORMATION
    	int id = c.getInt(0); //id
    	int type = c.getInt(1); //type
    	String name = c.getString(2); //name
    	String address = c.getString(3); //address
    	String cp = c.getString(4); //cp
    	String city = c.getString(5); //city
    	String country = c.getString(6); //country
    	int inf = c.getInt(7);
    	int sch = c.getInt(8);
    	int transp = c.getInt(9);
    	int we = c.getInt(10);
    	Double lon = c.getDouble(11); //long
    	Double lat = c.getDouble(12); //lat
    	Double alt = c.getDouble(13); //alt
    	int img = c.getInt(14); // LITTLE IMAGE
    	int img2 = c.getInt(15); // BIG IMAGE
    	int moreInfo = c.getInt(16);
    	c.close();
    	c = db.rawQuery(" SELECT text FROM text WHERE IDTEXT = '" + inf + "' AND LANGUAJE = '" + lang + "'", null);
    	c.moveToFirst();
    	String info = c.getString(0); //more info
    	c.close();
    	c = db.rawQuery(" SELECT text FROM text WHERE IDTEXT = '" + sch + "' AND LANGUAJE = '" + lang + "'", null);
    	c.moveToFirst();
    	String schedule = c.getString(0); //more info
    	c.close();
    	c = db.rawQuery(" SELECT text FROM text WHERE IDTEXT = '" + transp + "' AND LANGUAJE = '" + lang + "'", null);
    	c.moveToFirst();
    	String transport = c.getString(0); //more info
    	c.close();
    	c = db.rawQuery(" SELECT text FROM text WHERE IDTEXT = '" + we + "' AND LANGUAJE = '" + lang + "'", null);
    	c.moveToFirst();
    	String web = c.getString(0); //more info
    	c.close();
    	c = db.rawQuery(" SELECT text FROM text WHERE IDTEXT = '" + moreInfo + "' AND LANGUAJE = '" + lang + "'", null);
    	c.moveToFirst();
    	String moreInf = c.getString(0); //more info
    	c.close();
    	Poi aux = new Poi(id, type, name, address, cp, city, country, info, schedule, transport, web, lon, lat, alt, img, img2, moreInf);
    	return aux;
    }
    
    /**
     * Creates a Monument, with the specified ID and in the specified language. Also creates it POI part.
     * 
     * @param db 				The SQLITE object
     * @param identificador 	The id of the POI
     * @param lang 				The language //1 Spanish, 2 Catalan, 3 English
     * @return 					The created Monument
     */
    public Monument initializeMonumentById(SQLiteDatabase db, int identificador, int lang) {
    	Cursor c;
    	c = db.rawQuery(" SELECT * FROM " + "monument" + " WHERE ID = '" + identificador + "'", null);
    	c.moveToFirst();
    	String author = c.getString(1);
    	int pri = c.getInt(2);
    	c.close();
    	c = db.rawQuery(" SELECT text FROM " + "text" + " WHERE IDTEXT = '" + pri + "' AND LANGUAJE = '" + lang + "'", null);
    	c.moveToFirst();
    	String price = c.getString(0);
    	c.close();
    	Poi p = initializePoiById(db, identificador, lang);
    	Monument mon = new Monument(p.getId(), p.getType(), p.getName(), p.getAddress(), p.getCp(), p.getCity(), p.getCountry(), p.getInfo(), p.getSchedule(), p.getTransport(), p.getWeb(),p.getGpsLong(), p.getGpsLat(), p.getGpsAlt(), p.getImage(), p.getImage2(), p.getLinkMoreInformation(), author, price);
    	return mon;
    }
    
    /**
     * Creates an Attraction, with the specified ID and in the specified language. Also creates it POI part.
     * 
     * @param db 				The SQLITE object
     * @param identificador 	The id of the POI
     * @param lang 				The language //1 Spanish, 2 Catalan, 3 English
     * @return 					The created Attraction
     */
    public Attraction initializeAttractionById(SQLiteDatabase db, int identificador, int lang) {
    	Cursor c;
    	c = db.rawQuery(" SELECT * FROM " + "attraction" + " WHERE ID = '" + identificador + "'", null);
    	c.moveToFirst();
    	int pri = c.getInt(1);
    	String telf = c.getString(2);
    	c.close();
    	c = db.rawQuery(" SELECT text FROM " + "text" + " WHERE IDTEXT = '" + pri + "' AND LANGUAJE = '" + lang + "'", null);
    	c.moveToFirst();
    	String price = c.getString(0);
    	c.close();
    	Poi p = initializePoiById(db, identificador, lang);
    	Attraction att = new Attraction(p.getId(), p.getType(), p.getName(), p.getAddress(), p.getCp(), p.getCity(), p.getCountry(), p.getInfo(), p.getSchedule(), p.getTransport(), p.getWeb(),p.getGpsLong(), p.getGpsLat(), p.getGpsAlt(), p.getImage(), p.getImage2(), p.getLinkMoreInformation(), price, telf);
    	return att;
    }
    
    /**
     *  Creates a Museum, with the specified ID and in the specified language. Also creates it POI part.
     *  
     * @param db 				The SQLITE object
     * @param identificador 	The id of the POI
     * @param lang 				The language //1 Spanish, 2 Catalan, 3 English
     * @return 					The created Museum
     */
    public Museum initializeMuseumById(SQLiteDatabase db, int identificador, int lang) {
    	Cursor c;
    	c = db.rawQuery(" SELECT * FROM " + "museum" + " WHERE ID = '" + identificador + "'", null);
    	c.moveToFirst();
    	int e = c.getInt(1);
    	c.close();
    	c = db.rawQuery(" SELECT text FROM " + "text" + " WHERE IDTEXT = '" + e + "' AND LANGUAJE = '" + lang + "'", null);
    	c.moveToFirst();
    	String extra = c.getString(0);
    	c.close();
    	Poi p = initializePoiById(db, identificador, lang);
    	Museum mus = new Museum(p.getId(), p.getType(), p.getName(), p.getAddress(), p.getCp(), p.getCity(), p.getCountry(), p.getInfo(), p.getSchedule(), p.getTransport(), p.getWeb(),p.getGpsLong(), p.getGpsLat(), p.getGpsAlt(), p.getImage(), p.getImage2(), p.getLinkMoreInformation(), extra);
    	return mus;
    }
    
    /**
     *  Creates an InformationPoint, with the specified ID and in the specified language. Also creates it POI part.
     *  
     * @param db 				The SQLITE object
     * @param identificador 	The id of the POI
     * @param lang 				The language //1 Spanish, 2 Catalan, 3 English
     * @return 					The created InformationPoint
     */
    public InformationPoint initializeInformationPointById(SQLiteDatabase db, int identificador, int lang) {
    	Cursor c;
    	c = db.rawQuery(" SELECT * FROM " + "informationPoints" + " WHERE ID = '" + identificador + "'", null);
    	c.moveToFirst();
    	String telf = c.getString(1);
    	int e = c.getInt(2);
    	c.close();
    	c = db.rawQuery(" SELECT text FROM " + "text" + " WHERE IDTEXT = '" + e + "' AND LANGUAJE = '" + lang + "'", null);
    	c.moveToFirst();
    	String extra = c.getString(0);
    	c.close();
    	Poi p = initializePoiById(db, identificador, lang);
    	InformationPoint inf = new InformationPoint(p.getId(), p.getType(), p.getName(), p.getAddress(), p.getCp(), p.getCity(), p.getCountry(), p.getInfo(), p.getSchedule(), p.getTransport(), p.getWeb(),p.getGpsLong(), p.getGpsLat(), p.getGpsAlt(), p.getImage(), p.getImage2(), p.getLinkMoreInformation(), telf, extra);
    	return inf;
    }
    
    /**
     * Obtains an array with all the points of interest contained in the database, ordered by name, and in the given language.
     * 
     * @param db 				The SQLITE object
     * @param lang 				The language //1 Spanish, 2 Catalan, 3 English
     * @return 					The complete array of POIs
     */
    public ArrayList<Poi> getCompletList (SQLiteDatabase db, int lang) {
    	ArrayList<Poi> lista = new ArrayList<Poi>();
    	Cursor c;
    	c = db.rawQuery(" SELECT ID, TYPE FROM " + "poi ORDER by NAME", null);
    	c.moveToFirst();
    	while (!c.isAfterLast()) {
    		int id = c.getInt(0);
    		int tipo = c.getInt(1);
    		
    		switch(tipo) {
    			case 1:
    				Monument mon = initializeMonumentById(db, id,lang);
    				lista.add(mon);
    				break;
    			case 2:
    				Attraction a = initializeAttractionById(db, id,lang);
        			lista.add(a);
        			break;
    			case 3:
    				Museum mu = initializeMuseumById(db, id,lang);
        			lista.add(mu);
        			break;
    			case 4:
    				InformationPoint inf = initializeInformationPointById(db, id,lang);
        			lista.add(inf);
        			break;
        		default:
        			break;
    		}
    		c.moveToNext();	
    	}
    	c.close();
    	return lista;
    }
    
    /**
     * Checks the language.
     * 
     * @param db  			The SQLITE object
     * @return 				The current language
     */
    public int getLanguaje(SQLiteDatabase db) {
    	int lang;
    	Cursor c;
    	c = db.rawQuery(" SELECT LANGUAJE FROM preferences", null);
    	c.moveToFirst();
    	lang = c.getInt(0);
    	c.close();
    	return lang;
    }
    
    /**
     * Sets the language.
     * 
     * @param db 			The SQLITE object
     * @param lang			Determines the language which will be set on the database
     */
    public void setLanguaje(SQLiteDatabase db, int lang) {
    	db.execSQL("UPDATE preferences SET LANGUAJE=" + lang + " WHERE ID=0 ", null);
    }
}
